﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DataAccess;
using System.Data.SqlClient;

namespace AMS.DataAccess
{
    public class AccountInfoDAL
    {
        #region Member
        private readonly SQLHelper sqlHelper = new SQLHelper();
        #endregion

        #region Function
        /// <summary>
        /// 获取所有AllAccountInfo
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllAccountInfo()
        {
            string sql = " SELECT * FROM AccountInfo ";
            return sqlHelper.ExecuteDataTable(sql, false, null);
        }

        /// <summary>
        /// Get Account
        /// </summary>
        /// <returns></returns>
        public DataTable GetExpriedAccount()
        {
            string sql = " SELECT * FROM AccountInfo where Status IN(0,2)  ";
            return sqlHelper.ExecuteDataTable(sql, false, null);
            //access website to get account status info
            //access.AccessWebURL();
        }

        /// <summary>
        /// Add account
        /// </summary>
        /// <param name="model"></param>
        public void AccountAdd(AMS.Model.AccountInfo model)
        {
            int rows = 0;
            StringBuilder strSql = new StringBuilder();
            //
            strSql.AppendFormat("Insert into AccountInfo (ID,AccountName,PassWord,UserName,Email,CCEmail,Answer1,Answer2,Answer3,Answer4, UpdateDate,Status) values(NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',GETDATE(),1)"
                , model.AccountName, model.PassWord, model.UserName, model.Email, model.CCEmail, model.Answer1, model.Answer2, model.Answer3, model.Answer4);
            rows = sqlHelper.ExecuteCommand(strSql.ToString(), false, null);
        }

        /// <summary>
        /// Update account status
        /// </summary>
        /// <param name="id"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public int UpdateAccountStatus(string id, int status)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.AppendFormat("UPDATE AccountInfo SET UpdateDate='{0}',[Status]={1} WHERE ID='{2}'",
                DateTime.Now, status, id);
            return sqlHelper.ExecuteCommand(sbSql.ToString(), false, null);
        }
        /// <summary>
        /// judge if an account is exist in datasource
        /// </summary>
        /// <param name="account"></param>
        /// <returns></returns>
        public bool IsExist(string account)
        {
            //SqlParameter[] parameters = (SqlParameter[])sqlHelper.GetParameters(1);
            //parameters[0] = (SqlParameter)sqlHelper.GetParameter();
            //parameters[0].ParameterName = "@AccountName";
            //parameters[0].DbType = DbType.String;
            //if (string.IsNullOrEmpty(account.Trim()))
            //    parameters[0].Value = DBNull.Value;
            //else
            //    parameters[0].Value = account.Trim();

            //string sql = "select * from AccountInfo where REPLACE(AccountName,'\','') =REPLACE(@AccountName,'\','') ";
            string sql = string.Format("select * from AccountInfo where AccountName ='{0}' ", account.Trim());
            //string sql = string.Format("select * from AccountInfo where REPLACE(AccountName,'\','') =REPLACE('{0}','\','') ", account);
            DataTable dt = sqlHelper.ExecuteDataTable(sql.ToString(), false, parameters);
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                    return true;
                else return false;
            }
            else return false;
        }
        #endregion
    }
}
